Hi there,
This was posted to SQL where it 'truly' belongs but I got no answwer and
since it has a bit of database design in it (and a lot more people seem to
read [GENERAL]) I thought I'd try it here.
Consider a table like this:
brecard_id |code
----------------+----
IEGA18051999006 |COME
IPHA04031999004 |CRIB
IPHA04031999005 |COME
IPHA04031999005 |CRIB
IPHA26021999006 |SOLI
IPHA26021999010 |COME
IPHA26021999010 |SOLI
ISTL04031999001 |CRIB
IUCH03031999003 |COME
IUCH03031999003 |CRIB
IUCH03031999003 |MICR
IUCH03031999003 |SOLI
each combination of id and code is unique (they form a composite primary key)
but any brecard_id could have 1 or more codes associated with it
(theoretically with no upper boundary but let us say a maximum of 5 codes).
Is there a SELECT which will turn each of the codes for one brecard_id into
a column... ie.
brecard_id |code1|code2|code3|code4|code5
----------------+-----+-----+-----+-----+-----
IEGA18051999006 |COME | | | |
IPHA04031999004 |CRIB | | | |
IPHA04031999005 |COME |CRIB | | |
IPHA26021999006 |SOLI | | | |
IPHA26021999010 |COME |SOLI | | |
ISTL04031999001 |CRIB | | | |
IUCH03031999003 |COME |CRIB |MICR | |
IUCH03031999003 |SOLI | | | |
and here a a few more brainteasers for you gurus out there...
1) I'm actually not fussed about the order the codes appear in the columns,
but let's say the order mattered, would this affect the SELECT(s)?
2) Would it make the query easier if I knew the maximum number of codes one
brecard_id could have?
3) (this one for true Wizards -and Sorceresses, Herouth ;)- only) Could you
write a 'generalised' query which could cope with tables having variable
'maximum' numbers of codes associated with each brecard_id?
For the life of me I don't know how you'd do it in SQL, I have PL/pgSQL and
have started playing around with that but I'd hate to re-invent the wheel!
regards,
Stuart.
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk |
+-------------------------+--------------------------------------+
